* ----------------------------------------------------------------------------- *
* Natural Resource Rents, Local Taxes, and Government Performance: Evidence from
* Colombia
* By: Luis R. Martinez
* Name: 10-estimation.do
* Description: This do-file creates all the relevant variables for the analysis.
* ----------------------------------------------------------------------------- *

clear all 
cls 

use "${dpwork}\PANEL MUNIS.dta", clear


*------------------------------------------------------------------------------*
**# New Variables:
*------------------------------------------------------------------------------*

sort codmpio year
order codmpio muni year
 
gen inflacion=2.44 if year==2012
replace inflacion=3.73 if year==2011
replace inflacion=3.17 if year==2010
replace inflacion=2.00 if year==2009
replace inflacion=7.67 if year==2008
replace inflacion=5.69 if year==2007
replace inflacion=4.48 if year==2006
replace inflacion=4.85 if year==2005
replace inflacion=5.50 if year==2004
replace inflacion=6.49 if year==2003
replace inflacion=6.99 if year==2002
replace inflacion=7.76 if year==2001
replace inflacion=8.75 if year==2000	

//http://www.incp.org.co/indicadores-economicos-e-informacion-financiera-incp/indice-de-inflacion-1955-2011/

label var inflacion "Variacion anual IPC. Fuente:DANE"

* Another alternative is the IPC file in the DATA folder, which I calculated 
* based on the actual DANE monthly figures

gen definf=inflacion/100
replace definf=definf+1
gen definf1=definf if year==2005
replace definf1=definf*definf1[_n-1] if year>=2006
replace definf1=1 if year==2004
replace definf1=(1/definf[_n+1]) if year==2003
forvalues x=1/3{
	replace definf1=definf1[_n+1]/definf[_n+1] if year<2003
}
drop definf
rename definf1 definf
label var definf "deflator IPC. 2004=1. Source: DANE"

* Areas no municipalizadas
#d ;
gen corregimiento=(codmpio==91263)|(codmpio==91405)|(codmpio==91407)|
				  (codmpio==91430)|(codmpio==91460)|(codmpio==91530)|
				  (codmpio==91536)|(codmpio==91669)|(codmpio==91798)|
				  (codmpio==94343)|(codmpio==94886)|(codmpio==94885)|
				  (codmpio==94663)|(codmpio==94888)|(codmpio==94887)|
				  (codmpio==94884)|(codmpio==94883)|(codmpio==97889)|
				  (codmpio==97511)|(codmpio==97777);
#d cr

* Logs, reals and per capita amounts of money variables

* Variables in millions: 
#d;
foreach x of varlist y_total y_corr y_tax y_tax_pred y_tax_ica y_tax_gas 
					 y_tax_otr_post00 y_notax y_trf g_total g_corr g_fct 
					 g_svc g_gen g_trf g_int_debt deficit y_k y_k_rega 
					 y_k_trf y_k_cofin i i_fbkf i_otr def_total f f_cred 
					 f_cred_in f_cred_out ba_tot_vr {;
	gen `x'_r=(`x'*100)/(definf);
	gen l`x'_r=ln((`x'/definf));
	gen `x'_pcr=`x'_r/pop_total;
	label var `x'_pcr "`x' in 10K of 2004 COP per capita" ;
	gen l`x'_pcr=ln(1+`x'_pcr);
};
#d cr

* Variables in thousands
#d;
foreach x of varlist inv_crecusion-inv_en_vivienda sgp_educacion sgp_salud 
					 sgp_aguapot sgp_propgen {;
	gen `x'_r=`x'/(10*definf);
	gen l`x'_r=ln((`x'/definf)+1);
	gen `x'_pcr=`x'_r/pop_total;
	label var `x'_pcr "`x' in 10K of 2004 COP per capita" ;
	gen l`x'_pcr=ln(1+`x'_pcr);
};
#d cr

* Variables in pesos
#d ;
foreach x of varlist regalias regalias_oil regalias_oil_port regalias_oil_eco 	
					 regalias_oil_port_eco regalias_oil_total { ;
	gen `x'_r=`x'/(10000*definf);
	gen l`x'_r=ln((`x'/definf)+1);
	gen `x'_pcr=`x'_r/pop_total;
	label var `x'_pcr "`x' in millions of 2004 COP per capita" ;
	gen l`x'_pcr=ln(1+`x'_pcr);
};
#d cr

* Create logs
#d ;
foreach x of varlist pop_total cbtr-cntot tmi cobrs irca alumn_pri alumn_mpri 
					 alumn_hpri alumn_pri_u alumn_pri_r t_establ establ_total 
					  docen_total docen_prej docen_prij docen_secj 
					 admin_total cob01_bcg cob01_dpt cob01_pol cob1_tv nporvac01 
					 nporvac1{ ;
	gen l`x'=ln(1+`x') ;
	label var l`x' "log (1 + `x')" ;
} ;
#d cr

* Manipulations of cadastral variables
egen vt=rowtotal(vu vr),missing
label var vt "Total Value (Millones de pesos). Fuente:IGAC"

gen vt_r=vt/definf
gen lvt=ln(vt+1)
gen lvt_r=ln(vt_r+1)
egen nt=rowtotal(nu nr),missing
gen vt_ppr=vt_r/nt
gen lvt_ppr=ln(vt_ppr)
label var vt_ppr "Unit Value (Millions of 2004 COP). Fuente:IGAC"	

replace nt=nt/1000
label var nt "Num. of Properties (Miles). Fuente:IGAC"

gen lnt=ln(1+nt)
gen vt_pcr=vt_r/pop_total
label var vt_pcr "Total Value (Millions of 2004 COP per capita)"

gen lvt_pcr=ln(1+vt_pcr)
gen pred_rate_e=1000*y_tax_pred/vt
label var pred_rate_e "Effective property tax rate (permil)"

gen igac=(coddepto!=5&codmpio!=11001&codmpio!=76001) 
label var igac "IGAC responsable for cadastral"

* Target achievement rates
gen Dcnbas100=(cnbas>=100) if cnbas!=.
gen Dtmi165=(tmi<=16.5) if tmi!=.
gen Dcobrs100=(cobrs>=100) if cobrs!=.
gen Dirca05=(irca<=5) if irca!=.
gen Dacutot915=(acu_tot>91.5) if acu_tot!=.
gen Dalctot855=(alc_tot>85.5) if alc_tot!=.

* Outcomes values in 2005
foreach x of varlist cnbas Dcnbas100 tmi Dtmi165 cobrs Dcobrs100{
	gen temp=`x' if year==2005
	egen `x'05=max(temp),by(codmpio)
	drop temp
}	

gen temp=irca if year==2007
egen irca07=max(temp),by(codmpio)
drop temp

gen temp=Dirca05 if year==2007
egen Dirca0507=max(temp),by(codmpio)
drop temp
 
* Create vaccinations vars
#d ;
foreach x of varlist vacunas05 eda_ira05 embarazo05 defunciones05 vacunas01 
					 eda_ira01 embarazo01 defunciones01 {;
mvencode `x' if year>=2005&year<=2011,mv(0) over;
gen `x'_pc=`x'*1000/nacimientos;
gen l`x'_pc=ln(1+`x'_pc);
};
#d cr

* Create vars from births data
foreach x of varlist low_weight consultas0 consultas4 subsidiado pre_term {
	gen `x'_pc=`x'/nacimientos
	gen l`x'_pc=ln(1+`x'_pc)
}

gen lconsultas=ln(1+consultas)

* URosario conflict vars
gen Dgue=(gued>0) if gued!=.
gen gue_pc=gued*10000/pop_total
gen lgue_pc=ln(1+gue_pc)

gen Dpar=(pard>0) if pard!=.
gen par_pc=pard*10000/pop_total
gen lpar_pc=ln(1+par_pc)

* Fix errors in urban and rural cadastres
foreach x in u r{
	
	*This fixes vigencias jumping back to old values	(e.g. codmpio=13030)
	gen temp=c`x'-c`x'[_n-1] if codmpio==codmpio[_n-1]
	gen c`x'_fix=c`x'
	sum temp 
	local temp: di %4.3fc r(min)
	
	while `temp'<0{
		replace c`x'_fix=c`x'_fix[_n-1] if temp<0
		drop temp
		gen temp=c`x'_fix-c`x'_fix[_n-1] if codmpio==codmpio[_n-1]
		sum temp 
		local temp: di %4.3fc r(min)
	}
	drop temp 

	*This fixes jumps forward: cases where in 2002 vig appears as 2003	(e.g. codmpio=13458)	
	replace c`x'_fix=c`x'_fix[_n-1] if year<c`x'_fix&c`x'_fix!=.	
	
	*This fixes missings	(e.g. codmpio=13873 o 13620)
	count if c`x'_fix==.&c`x'_fix[_n+1]!=.&c`x'_fix[_n+1]<=year&codmpio==codmpio[_n+1]
	local temp=r(N)
	
	while `temp'>0{
		replace c`x'_fix=c`x'_fix[_n+1] if c`x'_fix==.&c`x'_fix[_n+1]!=.&		///
				c`x'_fix[_n+1]<=year&codmpio==codmpio[_n+1]
		count if c`x'_fix==.&c`x'_fix[_n+1]!=.&c`x'_fix[_n+1]<=year&			///
				codmpio==codmpio[_n+1]
		local temp=r(N)
	}
	
	count if c`x'_fix==.&c`x'_fix[_n-1]!=.&codmpio==codmpio[_n-1]
	
	local temp=r(N)
	
	while `temp'>0{
		replace c`x'_fix=c`x'_fix[_n-1] if c`x'_fix==.&c`x'_fix[_n-1]!=.&		///
				codmpio==codmpio[_n-1]
		count if c`x'_fix==.&c`x'_fix[_n-1]!=.&codmpio==codmpio[_n-1]
		local temp=r(N)
	}
	
	*This fixes delays: cases where in 2002 vig appears as 1998 but in 2003 
	*appears as 2002 (e.g. codmpio 13430)
	
	count if year>=c`x'_fix[_n+1]&c`x'_fix[_n+1]!=c`x'_fix&codmpio==codmpio[_n+1]
	
	local temp=r(N)
	
	while `temp'>0{
		replace c`x'_fix=c`x'_fix[_n+1] if year>=c`x'_fix[_n+1]&				///
				c`x'_fix[_n+1]!=c`x'_fix&codmpio==codmpio[_n+1]
		count if year>=c`x'_fix[_n+1]&c`x'_fix[_n+1]!=c`x'_fix&					///
				codmpio==codmpio[_n+1]
		local temp=r(N)
	}
	
	*This fixes cases where there is update in two consecutive years 
	*(I just set it to the later one) (e.g. codmpio=15455)
	
	replace c`x'_fix=c`x'_fix[_n-1] if year==c`x'_fix&year[_n+1]==c`x'_fix[_n+1] ///
			&codmpio==codmpio[_n+1]&codmpio==codmpio[_n-1]
	replace c`x'_fix=. if year==c`x'_fix&year[_n+1]==c`x'_fix[_n+1]				///
		&codmpio==codmpio[_n+1]&codmpio!=codmpio[_n-1]	
		// cases like codmpio=52435 where the two years are 1993 and 1994
}

* Generate age variables
gen age=year-cu_fix+1
gen ageatu=age
replace ageatu=ageatu[_n-1]+1 if ageatu==1&ageatu[_n-1]!=.
replace ageatu=. if ageatu==1&ageatu[_n-1]==.

gen age_r=year-cr_fix+1
gen ageatu_r=age_r
replace ageatu_r=ageatu_r[_n-1]+1 if ageatu_r==1&ageatu_r[_n-1]!=.
replace ageatu_r=. if ageatu_r==1&ageatu_r[_n-1]==.

gen old=(age>=6)
gen oldatu=(ageatu>=6)
gen old_r=(age_r>=6)
gen oldatu_r=(ageatu_r>=6)

gen unf=(age==.&igac==1)
gen unf_r=(age_r==99&igac==1)

* Generate update variables
gen N1u=(year==cu_fix)
gen N1u_raw=N1u
gen N1r=(year==cr_fix)
gen N1r_raw=N1r
gen N1any=max(N1u, N1r)
gen N1both=N1u*N1r

gen O1any=N1any[_n+1] if codmpio==codmpio[_n+1]
gen new=(age>=1&age<=5)  
gen new_r=(age_r>=1&age_r<=5)

foreach x of numlist 1997/2011{
	gen temp=((N1u_raw==1|N1r_raw==1)&year==`x')
	egen up_`x'any=max(temp),by(codmpio)
	drop temp	
}

gen post_update0610any=(up_2006any==1&year>=2006)|(up_2007any==1&year>=2007)|	///
					   (up_2008any==1&year>=2008)|(up_2009any==1&year>=2009)|	///
					   (up_2010any==1&year>=2010)
					   
gen updater0610any=(up_2006any==1)|(up_2007any==1)|(up_2008any==1)|				///
				   (up_2009any==1)|(up_2010any==1)

bysort codmpio (year): gen N1_cum05=sum(N1any) if year>=2005

* Create urban and rural shares based on year 2000 values
gen temp=vr/vt if year==2000
replace temp=vr/vt if year==2001&temp[_n-1]==.&vr/vt!=.
replace temp=vr/vt if year==2002&temp[_n-1]==.&temp[_n-2]==.&vr/vt!=.
replace temp=vr/vt if year==2003&temp[_n-1]==.&temp[_n-2]==.&temp[_n-3]==.&vr/vt!=.
egen shr=max(temp),by(codmpio)
gen shu=1-shr
drop temp

* Use these shares to create weighted update dummies
forvalues x=2002/2011{
	gen N1_`x'w=0
	replace N1_`x'w=1 if N1u==1&N1r==1&year==`x'
	replace N1_`x'w=N1u*shu if N1u==1&N1r==0&year==`x'
	replace N1_`x'w=N1r*shr if N1r==1&N1u==0&year==`x'
	bysort codmpio (year): gen post`x'w=sum(N1_`x'w)
	drop N1_`x'w
}

* Vars for cadastral update event study
gen b1=(post_update0610any==0&post_update0610any[_n+1]==1&codmpio==codmpio[_n+1])
gen b2=(b1==0&b1[_n+1]==1&codmpio==codmpio[_n+1])
gen b3=(b2==0&b2[_n+1]==1&codmpio==codmpio[_n+1])
gen b4p=(updater0610any==1&post_update0610any==0&b1+b2+b3==0)
gen a1=(post_update0610any==1&post_update0610any[_n-1]==0&codmpio==codmpio[_n-1])
gen a2=(a1==0&a1[_n-1]==1&codmpio==codmpio[_n-1])
gen a3=(a2==0&a2[_n-1]==1&codmpio==codmpio[_n-1])
gen a4p=(updater0610any==1&post_update0610any==1&a1+a2+a3==0)

gen pre_trend=b4p+b3+b2

* Classify munis into fixed
gen temp=(cu!=cu_fix&cu!=.)
egen fixed_u=max(temp),by(codmpio)
label var fixed_u "Vigencia urbana corregida por lo menos 1 año"
drop temp
gen temp=(cr!=cr_fix&cr!=.)
egen fixed_r=max(temp),by(codmpio)
label var fixed_r "Vigencia rural corregida por lo menos 1 año"
drop temp

gen O1plan=0

* Add info from IGAC wishlist for 2012 and 2013
#d ;
foreach x of numlist 8296 8549 8638 13042 13062 13074 13160 13490 13600 13655 
					 13688 15001 15176 17050 17662 18094 18150 18205 18247 18756 
					 19001 19533 19698 20011 20295 20550 20614 20710 20770 23068 
					 23419 23555 25175 25297 25402 25736 25785 25799 41001 41551 
					 44035 44078 44090 44378 44430 44650 47001 47053 47541 47980 
					 50001 85430 52001 52405 86001 54001 63001 63302 66045 66440 
					 66572 66682 27099 27135 27810 68081 68092 68152 68167 68207 
					 68250 68318 68780 70215 70429 70713 70771 73001 73168 73275 
					 73555 73616 76020 76111 76670 {;
	replace O1plan=1 if codmpio==`x'&year==2011;
};

foreach x of numlist 8001 8433 8638 13001 13670 13894 13838 13188 13222 15001 
					 15759 15238 15407 15798 15187 15531 15764 15494 17001 17042 
					 18001 19001 20001 23001 25799 25290 25183 25035 25175 25599 
					 25769 25286 91001 44001 44430 41001 41551 47001 50325 50568 
					 50573 99001 99524 99624 99773 86001 54001 54250 63001 63272 
					 63690 66001 27001 68001 68418 70001 73001 73148 73624 
					 76109 { ;
	replace O1plan=1 if codmpio==`x'&year==2012;
};
 
#d cr
* Creating instrument for oil price
gen price_oil_cop_r=price_oil*trm/definf
replace price_oil_cop_r=price_oil_cop_r/1000
label var price_oil_cop_r "Price of oil in thousands of 2004 COP"

gen temp1=cond(year==2004,pop_total,0)
egen pop04=max(temp1), by(codmpio)
gen temp2=price_oil_cop_r if year==2004
egen price_oil_cop_r04=max(temp2),by(codmpio)
drop temp*

egen temp=mean(regalias_oil_total_r) if year>=2000&year<=2004,by(codmpio)
egen roil00_04=max(temp), by(codmpio)
egen temp1=mean(pop_total) if year>=2000&year<=2004,by(codmpio)
egen pop00_04=max(temp1), by(codmpio)
gen roilpcravg=roil00_04/pop00_04
drop temp* roil00_04 

gen temp=cond(year==2000,regalias_oil_total_r,0)
egen roil00=max(temp), by(codmpio)
gen temp1=cond(year==2000,pop_total,0)
egen pop00=max(temp1), by(codmpio)
gen temp2=price_oil_cop_r if year==2000
egen price_oil_cop_r00=max(temp2),by(codmpio)
drop temp*
gen roilpcr00=roil00/pop00

gen ind_price_oil_cop_r=price_oil_cop_r/price_oil_cop_r04
gen roilpcravg_iprcopr=roilpcravg*ind_price_oil_cop_r

* Oil royalties measure (ignoring ports)
gen r_oil=regalias_oil+regalias_oil_eco
gen r_oil_r=r_oil/(10000*definf)
egen temp=mean(r_oil_r) if year>=2000&year<=2004,by(codmpio)
egen r_oil00_04=max(temp), by(codmpio)
gen r_oilpcravg=r_oil00_04/pop00_04
drop temp

* Create year FE x oil
forvalues x=2006/2011{
	gen roilpcravg_`x'=cond(year==`x',r_oilpcravg,0) if r_oilpcravg!=.
	gen lroilpcravg_`x'=cond(year==`x',ln(1+r_oilpcravg),0) if r_oilpcravg!=.
	gen Droy_`x'=cond(year==`x'&r_oilpcravg>0,1,0) if r_oilpcravg!=.
}

* Oil instruments
gen roy=r_oilpcravg*price_oil_cop_r
gen lroy=ln(1+roy)
gen roy_ipoil=r_oilpcravg*ind_price_oil_cop_r
gen temp=cond(year==2000,r_oil_r,0)
egen r_oil00=max(temp), by(codmpio)
gen r_oilpcr00=r_oil00/pop00
drop temp

* Cadastral updates in oil_rich
gen up_muni_oil0610=cond(r_oilpcravg>0,post_update0610any,0) if r_oilpcravg!=.
 
gen president=1 if year>=2000&year<=2003
replace president=2 if year>=2004&year<=2007
replace president=3 if year>=2008&year<=2011
replace president=4 if year==2012
label define pres 1 "Pastrana" 2 "Uribe 1" 3 "Uribe 2" 4 "Santos"
label values president pres
label var president "1=Pastrana, 2=Uribe1, 3=Uribe2, 4=Santos"
 
* Generate political cycle variables
gen term=0
replace term=1 if year==1995|year==1996|year==1997
replace term=2 if year==1998|year==1999|year==2000
replace term=3 if year==2001|year==2002|year==2003
replace term=4 if year==2004|year==2005|year==2006|year==2007
replace term=5 if year==2008|year==2009|year==2010|year==2011
replace term=6 if year==2012
label var term "Grouping variable for mayoral terms"
egen idmpio=group(codmpio term)
label var idmpio "Unique identifier for each term and each municipality"

* Political chars: 
#d ;
gen myr_reelect_eff=1-myr_ch_prty_eff; 

foreach x of varlist myr_sh_winner_v_eff myr_sh_vblank_eff myr_left_eff 
					 myr_left_rwc_eff myr_para_eff myr_lib_eff myr_cons_eff 
					 myr_new_parties_eff myr_sh_null_r1_eff myr_sh_empty_r1_eff 
					 myr_ncandidates_myr_eff myr_hhi_norm_eff myr_sh_inc_eff 
					 myr_margin_eff myr_reelect_eff gob_myr{;
replace `x' =. if myr_no_election_eff==1 ; 
};

replace pct_myr_cons =. if no_myr==1|no_cons==1  ;
replace myr_cons_over_50=. if no_myr==1|no_cons==1 ; 

#d cr 
* Fill in missing values
foreach x of varlist peagrariac peofdereci pepsaludc {
	egen temp=max(`x'),by(codmpio)
	replace `x'=temp if `x'==.
	drop temp
}

* Create valid sample
** i. Non-zero population
gen valid=(pop_total>0&pop_total!=.)

** ii No missing data for main outcomes or sources of revenue
foreach x of varlist y_total_pcr g_total_pcr i_pcr cnbas tmi cobrs{
	replace valid=0 if `x'==0|`x'==.
}
* iii only igac
replace valid=0 if igac==0|corregimiento==1

** iv drop new munis without educ data y Belen de Bajira
replace valid=0 if codmpio==13490|codmpio==19300|codmpio==23682|codmpio==23815|codmpio==27086|codmpio==11001|codmpio==76001|codmpio==5001 

* Department-year grouping for two-way clustering
egen dpt_year=group(coddepto year)
*create numerical muni category var
egen category=group(categoria)

* Create controls
gen murders_pc=100000*homicidios/pop_total
label var murders_pc "Murders per 1000 inh."
gen lmurders_pc=ln(1+murders_pc)
gen faccion_pc=1000*faccion/pop_total
label var faccion_pc "New Familias en Accion per 1000 inh"
gen lfaccion=ln(faccion+1)
gen lfaccion_pc=ln(faccion_pc+1)
replace cons_com=0 if year==2011
gen Dcons_com=(cons_com>0&cons_com!=.)
label var Dcons_com "Dummy=1 if there was an Uribe Concejo comunitario"

* Housekeeping
compress
sort codmpio year
xtset codmpio year

** Labels:
label var codmpio "ID Municipality"
label var coddepto "ID Department"
label var year "Year"
label var corregimiento "Dummy: Non Municipality Area"
label var valid "Dummy: In sample"
label var igac "Dummy: IGAC Cadastral Update"
label var category "Municipal Category"
label var post_update0610any "Cadastral Update Absorbing State" 
label var lroy "Log: Oil Instrument"
label var roy "Oil Instrument"
label var roy_ipoil "Oil Instrument with Price Index"
label var roilpcravg_iprcopr "Oil Instrument"
label var ly_tax_pred_pcr "Log: Property Tax Revenue"
label var ly_k_rega_pcr "Log: Natural Resource Royalties"
label var y_tax_pred_pcr "Property Tax Revenue"
label var y_k_rega_pcr "Natural Resource Royalties"
label var dpt_year "Group: Year and Department"
label var ly_total_pcr "Log: Total Revenue"
label var lg_total_pcr "Log: Total Spending"
label var li_pcr "Log: Investment"
label var lcnbas "Log: Educational Enrolment"
label var cnbas "Educational Enrolment"
label var lcobrs "Log: Health Subsidies"
label var cobrs "Health Subsidies"
label var lirca "Log: Water Pollution"
label var irca "Water Pollution"
label var Dcnbas100 "Target Achievement Dummy: Educational Enrolment"
label var Dcobrs100 "Target Achievement Dummy: Health Subsidies"
label var Dirca05 "Target Achievement Dummy: Water Pollution"
label var lconsultas0_pc "Log Share of Births: No Visits"
label var consultas0_pc "Share of Births: No Visits"
label var llow_weight_pc "Log Share of Births: Low Weight"
label var low_weight_pc "Share of Births: Low Weight"
label var lcob01_bcg "Log Infant Immunization Rates: BCG"
label var cob01_bcg "Infant Immunization Rates: BCG"
label var lcob01_dpt "Log Infant Immunization Rates: DPT"
label var cob01_dpt "Infant Immunization Rates: DPT"
label var lcob01_pol "Log Infant Immunization Rates: Polio"
label var cob01_pol "Infant Immunization Rates: Polio"
label var ltmi "Log Infant Mortality Rate: Official"
label var tmi "Infant Mortality Rate: Official"
label var ldefunciones01_pc "Log Infant Mortality Rate: Own"
label var defunciones01_pc "Infant Mortality Rate: Own"
label var leda_ira01_pc "Log Infant Mortality Rate: AD/URI"
label var eda_ira01_pc "Infant Mortality Rate: AD/URI"
label var b4p "Years Since Cadastral Update: <-4"
label var b3  "Years Since Cadastral Update: -3"
label var b2  "Years Since Cadastral Update: -2"
label var a1  "Years Since Cadastral Update: +1"
label var a2  "Years Since Cadastral Update: +2"
label var a3  "Years Since Cadastral Update: +3" 
label var a4p "Years Since Cadastral Update: >+4"
label var Droy_2006 "Dummy Royalties - 2006" 
label var Droy_2007 "Dummy Royalties - 2007"
label var Droy_2008 "Dummy Royalties - 2008"
label var Droy_2009 "Dummy Royalties - 2009"
label var Droy_2010 "Dummy Royalties - 2010"
label var Droy_2011 "Dummy Royalties - 2011"
label var y_total_pcr "Total Revenue"
label var g_total_pcr "Total Spending"
label var i_pcr "Total Investment"
label var acu_tot "Aqueduct Access (%)"
label var alc_tot "Sewerage Access (%)"
label var updater0610any "Dummy: Cadastral Update"
label var r_oilpcravg "Average Royalties 00-04 Per Capita"
label var Dtmi165 "Target Achievement Dummy: Infant Mortality"
label var Dacutot915 "Target Achievement Dummy: Infant Mortality"
label var Dalctot855 "Target Achievement Dummy: Sewerage Access"
label var irca07 "Log: Water Pollution Index 07"
label var pop_total "Total Population"
label var indrural "Rural Population (%)"
label var nbi "Unmet Basic Needs"
label var cob_eelectr "Electricity Access (%)"
label var Dgue "Dummy: Any Guerrilla Event"
label var Dpar "Dummy: Any Paramilitary Event"
label var notarias "Dummy: Presence of Notary"
label var peofdereci "Dummy: Presence of Tax Office"
label var cons_com "Dummy: Community Council"
label var peagrariac "Dummy: Presence of Agricultural Bank"
label var area "Area - Sqr Km"
label var altura "Altitude - Meters"
label var disbogota "Distance to Bogota - Km"
label var discapital "Distance to Department Capital - Km"
label var cnbas05 "Net Enrolment Rate in Basic Education"
label var cobrs05 "Subsidized Health Insurance (% of poor)"
label var Dcnbas10005 "Dummy: Education enrolment > 100% in 2005"
label var Dcobrs10005 "Dummy: Health insurance  > 100% in 2005"
label var Dirca0507 "Dummy: IRCA water quality index <5 in 2007"
label var pre_trend "Dummy: Before Cadastral Update"
label var ly_tax_ica_pcr "Log: Business Tax Revenue"
label var ly_tax_gas_pcr "Log: Gasoline Tax revenue"
label var ly_tax_otr_post00_pcr "Log: Other Tax Revenue"
label var ly_notax_pcr "Log: Non-tax Current Revenues"
label var ly_trf_pcr "Log: Transfers (current revenues)"
label var ly_k_trf_pcr "Log: Transfers (capital)"
label var ly_k_cofin_pcr "Log: Co-financing"
label var lg_fct_pcr "Log: Operating expenses"
label var lg_int_debt_pcr "Log: Interest debt payments"
label var li_fbkf_pcr "Log: Gross fixed capital formation"
label var li_otr_pcr "Log: Other investment"
label var lsgp_educacion_pcr "Log: SGP transfers (education)"
label var lsgp_salud_pcr "Log: SGP transfers (health)"
label var lsgp_aguapot_pcr "Log: SGP transfers (water)"
label var lsgp_propgen_pcr  "Log: SGP transfers (other)"
label var lmurders_pc "Log: Homicide Rate" 
label var Dcons_com "Dummy: Community Council"
label var lba_tot_vr_pcr "Log: Agricultural Loan Disbursements"
label var lfaccion "Log : Families in CCT program"
label var lgue_pc "Log: Guerrilla Activity"
label var lpar_pc "Log: Paramilitary Activity"
label var myr_sh_winner_v_eff "Winner's vote share"
label var myr_sh_vblank_eff "Unused vote share"
label var myr_no_election_eff "D(missing data)"
label var myr_left_eff "D(Left-wing winner)"
label var myr_left_rwc_eff "D(Left-wing winner - RWC)"
label var myr_para_eff "D(Pro-paramilitary winner)"
label var myr_lib_eff "D(Liberal winner)"
label var myr_cons_eff "D(Conservative winner)"
label var myr_new_parties_eff "D(New parties winner)"
label var myr_sh_null_r1_eff "Invalid vote share"
label var myr_sh_empty_r1_eff "Blank vote share"
label var myr_ncandidates_myr_eff "Number of candidates"
label var myr_hhi_norm_eff "HHI votes"
label var myr_sh_inc_eff "Incumbent party's vote share"
label var myr_margin_eff "Incumbent party's vote share"
label var myr_reelect_eff "D(Incumbent party re-elected)"
label var pres_sh_vblank_r1_eff "Unused vote share" 
label var pres_sh_null_r1_eff "Unused vote share"
label var pres_sh_empty_r1_eff "Blank vote share"
label var pres_sh_lib_r1_eff "Liberal party's vote share"
label var pres_sh_cons_r1_eff "Conservative party's vote share"
label var pres_sh_winner_r1_eff "Winner's vote share"
label var pres_sh_left_r1_eff "Left-wing parties' vote share"
label var pres_sh_uri_r1_eff "Uribe candidate vote share"
label var pres_sh_inc_r1_eff "Incumbent party's vote share"
label var no_cons "D(missing data)"
label var parties_seat "Number of parties per seat"
label var hhi_cons_norm "HHI seats"
label var pct_myr_cons "Sh. seats: mayor's party"
label var cons_sh_left "Sh. seats: left-wing parties"
label var cons_sh_para "Sh. seats: pro-paramilitary parties"
label var cons_sh_new  "Sh. seats: new parties"
label var cons_sh_lib  "Sh. seats: new parties"
label var cons_sh_cons "Sh. seats: Conservative party"
label var sh_winner_gob "Winner's vote share"
label var gob_myr "D(Winner from mayor's party)"
label var D_Coca "D(Coca crops)"
label var N1any "Any Cadastral Update"
label var ageatu "Years since last update"
label var ageatu_r "Years since last update - Revised"
label var vt_ppr "Property Value"
label var cu_fix "Cadastral Year Update - Urban"
label var cr_fix "Cadastral Year Update - Rural"
label var president "President"
label var N1u "Number of Properties - Urban"
label var N1r "Number of Properties - Rural"
label var N1both "Number of Properties - Both"
label var post2002w "Update cohort - 2002"
label var post2003w "Update cohort - 2003"
label var post2004w "Update cohort - 2004"
label var post2005w "Update cohort - 2005"
label var post2006w "Update cohort - 2006"
label var post2007w "Update cohort - 2007"
label var post2008w "Update cohort - 2008"
label var post2009w "Update cohort - 2009"
label var post2010w "Update cohort - 2010"
label var post2011w	"Update cohort - 2011"

* Short Data Set: Main Results

preserve

* Relevant Variables:
{


#d ;
keep 
/* 
	Main Tex:
																				*/
	 codmpio coddepto year term corregimiento valid igac category 
	 post_update0610any lroy roy_ipoil roy roilpcravg_iprcopr  
	 ly_tax_pred_pcr ly_k_rega_pcr y_tax_pred_pcr  y_k_rega_pcr dpt_year
	 ly_total_pcr lg_total_pcr li_pcr lcnbas cnbas lcobrs cobrs lirca irca
	 Dcnbas100 Dcobrs100 Dirca05 lconsultas0_pc consultas0_pc llow_weight_pc
	 low_weight_pc lcob01_bcg cob01_bcg lcob01_dpt cob01_dpt lcob01_pol
	 cob01_pol ltmi tmi ldefunciones01_pc defunciones01_pc leda_ira01_pc
	 eda_ira01_pc b4p b3 b2 a1 a2 a3 a4p Droy_2006 Droy_2007 Droy_2008 Droy_2009 
	 Droy_2010 Droy_2011
/* 
	Appendix
																				*/
	 y_total_pcr g_total_pcr i_pcr acu_tot alc_tot updater0610any r_oilpcravg 
	 Dtmi165 Dacutot915 Dalctot855 irca07 pop_total indrural nbi acu_tot alc_tot 
	 cob_eelectr Dgue Dpar notarias peofdereci cons_com peagrariac area altura 
	 disbogota discapital cnbas05 cobrs05 Dcnbas10005 Dcobrs10005 Dirca0507 
	 pre_trend ly_tax_pred_pcr ly_tax_ica_pcr ly_tax_gas_pcr 
	 ly_tax_otr_post00_pcr ly_notax_pcr ly_trf_pcr ly_k_rega_pcr lroy 
	 ly_k_trf_pcr ly_k_cofin_pcr lg_fct_pcr lg_int_debt_pcr li_fbkf_pcr 
	 li_otr_pcr lconsultas0_pc llow_weight_pc lcob01_bcg lcob01_dpt lcob01_pol 
	 ltmi ldefunciones01_pc lsgp_educacion_pcr lsgp_salud_pcr lsgp_aguapot_pcr 
	 lsgp_propgen_pcr  lmurders_pc Dcons_com lba_tot_vr_pcr lfaccion Dgue 
	 lgue_pc Dpar lpar_pc lcnbas ltmi leda_ira01_pc lcobrs myr_sh_winner_v_eff
	 myr_sh_vblank_eff myr_no_election_eff myr_left_eff myr_left_rwc_eff 
	 myr_para_eff myr_lib_eff myr_cons_eff myr_new_parties_eff  
	 myr_sh_null_r1_eff myr_sh_empty_r1_eff myr_ncandidates_myr_eff 
	 myr_hhi_norm_eff myr_sh_inc_eff myr_margin_eff myr_reelect_eff 
	 pres_sh_vblank_r1_eff pres_sh_null_r1_eff pres_sh_empty_r1_eff 
	 pres_sh_lib_r1_eff pres_sh_cons_r1_eff pres_sh_winner_r1_eff 
	 pres_sh_left_r1_eff pres_sh_uri_r1_eff pres_sh_inc_r1_eff no_cons 
	 parties_seat hhi_cons_norm pct_myr_cons cons_sh_left cons_sh_para 
	 cons_sh_new cons_sh_lib cons_sh_cons sh_winner_gob gob_myr D_Coca N1any 
	 ageatu ageatu_r vt_ppr cu_fix cr_fix president N1u N1r N1both 
	 post2002w-post2011w;
#d cr

	
}

save "${dpwork}\ESTIMATIONS.dta", replace

restore 

*------------------------------------------------------------------------------*
**# Create muni-term datasets
*------------------------------------------------------------------------------*

* Sample consistency
drop if pop_total==0|pop_total==.|igac==0|corregimiento==1|codmpio==13490|		///
		codmpio==19300|codmpio==23682|codmpio==23815|codmpio==27086|			///
		codmpio==11001|codmpio==76001|codmpio==5001 

* Collapse to muni-period level
foreach v of var * {
local l`v' : variable label `v'
      if `"`l`v''"' == "" {
	local l`v' "`v'"
	}
}
 
collapse (mean) y_k_rega_pcr roilpcr00 r_oilpcr00 price_oil_cop_r y_tax_pred_pcr ///
		 (max) N1any myr_left myr_lib myr_cons myr_new_parties igac 			///
			   corregimiento Dgue Dpar, by(codmpio coddepto term)
 
foreach v of var * {
	label var `v' `"`l`v''"'
}			   
			   
* Keep periods 2001-2003, 2004-2007, 2008-2011 and redefine period for merge
keep if term==3|term==4|term==5
gen period=.
replace period=1 if term==3
replace period=2 if term==4
replace period=3 if term==5
label define per 1 "2001-2003" 2 "2004-2007" 3 "2008-2011" 4 "2012-2015"
label values period per
drop term

* Combine w/ Procuraduria data
merge 1:1 codmpio period using "${dpraw}\11-procuraduria\procuraduria 2005-2015 agr.dta"
	drop if _merge==2 //92% of cases correspond to 2012-2015 period
	drop _merge
	mvencode alcalde-alcalde_sum,mv(0) over //missings meanzero cases

* Political alignment of mayor
gen myr_other=(myr_left==0&myr_lib==0&myr_cons==0&myr_new_parties==0)
mvencode myr_left myr_lib myr_cons myr_new_parties myr_other,mv(0) over

* Create main vars
egen dpt_period=group(coddepto period)
gen ly_k_rega_pcr=ln(1+y_k_rega_pcr)
gen ly_tax_pred_pcr=ln(1+y_tax_pred_pcr)
gen lr_oilpcr00_prcopr=ln(1+(r_oilpcr00*price_oil_cop_r))
bysort codmpio (period): gen post_sum=sum(N1any)

* Combine w/ political data
merge 1:1 codmpio period using "${dpraw}\15-elections\summary_local.dta"
drop if _merge==2	// this drops the 2000 results
drop _merge

gen lvotes_valid=ln(1+votes_valid)
gen lvotes_total=ln(1+votes_total)
gen lncandidates_myr=ln(1+ncandidates_myr)
gen lparties_seat=ln(1+parties_seat)
gen myr_trad=myr_lib+myr_cons
gen reelect=1-ch_prty
gen no_election=(no_myr==1|no_myr==.|no_cons==1)
replace pct_myr_cons=. if no_myr==1

compress

#d ;
keep codmpio coddepto corregimiento igac post_sum  lr_oilpcr00_prcopr dpt_period
	 period ly_tax_pred_pcr ly_k_rega_pcr alcalde alcalde_sum alcalde_1 
	 alcalde_2 alcalde_3 price_oil_cop_r r_oilpcr00 y_tax_pred_pcr y_k_rega_pcr;
# d cr

** Label variables:
label var r_oilpcr00 "Oil Per Capita 2000" 
label var period "Election Period"
label var alcalde "Disciplinary Prosecutions: D(Mayor Prosecuted)"
label var alcalde_1 "Disciplinary Prosecutions: D(Mayor prosecuted) - Spending"
label var alcalde_2 "Disciplinary Prosecutions: D(Mayor prosecuted) - Politics"
label var alcalde_3 "Disciplinary Prosecutions: D(Mayor prosecuted) - Other"
label var alcalde_sum "Disciplinary Prosecutions: Number of Cases"
label var dpt_period "Group: Department - Year"
label var ly_k_rega_pcr "Log: Natural Resource Royalties"
label var ly_tax_pred_pcr "Log: Property Tax Revenue"
label var lr_oilpcr00_prcopr "Log: Oil royalties x Oil price"
label var post_sum "D(Updated cadastre)"

save "${dpwork}\ESTIMATIONS-TERM.dta", replace

